#!/usr/bin/env python
# -*- coding: utf-8 -*-
from io import DEFAULT_BUFFER_SIZE
from tkinter import messagebox
import tkinter.filedialog
import openpyxl
import datetime
from openpyxl import Workbook
import re
import argparse
from log import Logger
import sys
import os
# global init
ASB = {"1002013301":"01-01-0173-7",
        "1002010201":"11030101040005001",
        "1002010101":"0200292019100001951"} #会计科目银行账号对照表，nccid->banck_account
# INC = [140,232,250,260,273,275,288,343,358,369,370,428,440,497,500];
DEFAULT_NCC_OUTPUT = "ncc_"+datetime.datetime.now().strftime("%Y%m%d")+".xlsx"
DEFAULT_U8_ERROR_OUTPUT = "u8_"+datetime.datetime.now().strftime("%Y%m%d")+".error.xlsx"
logfile = datetime.datetime.now().strftime("%Y%m%d") + ".log"
log = Logger(logfile,level='debug')

def resource_path(relative_path):
    if getattr(sys, 'frozen', False): #是否Bundle Resource
        base_path = sys._MEIPASS
    else:
        base_path = os.path.abspath(".")
    return os.path.join(base_path, relative_path) 
class AV(object):
    List=[]
    """
    会计凭证,通过U8Excel建立凭证对象进而导入到NCC中
    """
    def __init__(self, vnum, owner,date,desc,
        account_subject, debit_orig, debit_local,
        credit_orig,credit_local,
        depart,trader) -> None:
        super().__init__()
        self.ab = "01-005-05-CEC" #核算账簿
        self.vtype = "01" #凭证类别
        self.vn = vnum #凭证号
        self.owner = owner #制单人
        self.date = date #制单日期
        self.desc = desc #摘要
        self.account_subject = account_subject #科目
        self.ctype = "人民币" #币种
        self.debit_orig = debit_orig #原币借方金额
        self.debit_local = debit_local #本币借方金额
        self.credit_orig = credit_orig #原币贷方金额
        self.credit_local = credit_local #本币贷方金额
        self.depart = depart #部门编号
        self.trader = trader #客商信息

    def isDebit(self):
        return self.debit_orig>0

class AS(object):
    List = {}
    """
    会计科目,包含U8科目ID以及NCC科目ID和相关信息
    """
    def __init__(self, u8id, nccid, u8name,name,
        is_cash_flow = False, 
        is_trader = False,is_depart = False,is_busi_type = False) -> None:
        super().__init__()
        self.u8id = u8id #u8id
        self.nccid = nccid # ncc id
        self.u8name = u8name # u8 科目名称
        self.name = name # ncc name
        self.is_cash_flow = is_cash_flow # 是否现金流
        self.is_trader = is_trader #是否填客商
        self.is_depart = is_depart # 是否填部门
        self.is_busi_type = is_busi_type # 是否填业务类型

    def isBankAccount(self):
        """根据nccid判断是否是银行账号"""

class Trader(object):
    List = {}
    """
    企业信息，包含企业名称与纳税人识别号
    """
    def __init__(self, name, txid) -> None:
        self.name = name
        self.txid = txid

class Project(object):
    List = {}
    """ 会计项目 """
    def __init__(self, u8id, nccid, name) -> None:
        super().__init__()
        self.u8id = u8id
        self.nccid = nccid
        self.name = name

def load_traders():
    book = openpyxl.load_workbook(resource_path(os.path.join("res",'traders.xlsx')))
    sheet = book.active
    for row in sheet.iter_rows(min_row=2, min_col=1):
        trader = Trader(row[2].value, row[1].value)
        Trader.List[trader.name] = trader

def load_projects():
    book = openpyxl.load_workbook(resource_path(os.path.join("res",'projects.xlsx')))
    sheet = book.active
    for row in sheet.iter_rows(min_row=2, min_col=1):
        u8id = row[0].value
        name = row[1].value
        nccid = row[2].value
        if u8id is not None:
            p = Project(u8id,nccid,name)
            Project.List[u8id] = p

def load_account_subjects():
    """加载会计科目对照表"""
    book = openpyxl.load_workbook(resource_path(os.path.join("res",'account_subjects.xlsx')))
    sheet = book.active
    for row in sheet.iter_rows(min_row=2, min_col=1):
        nccid = str(row[2].value)
        u8id = str(row[5].value)
        u8name = row[6].value
        name = row[3].value
        ff = row[4].value
        if len(nccid)>0 and len(u8id)>0:
            is_depart = False
            is_trader = False
            is_cash_flow = False
            is_busi_type = False
            if ff is not None:
                is_depart = re.search("部门\s*\(必填", ff) is not None
                is_trader = re.search("客商\s*\(必填", ff) is not None
                is_cash_flow = re.search("现金流量\s*\(必填", ff) is not None
                is_busi_type = re.search("业务类型\s*\(必填", ff) is not None
            ss = AS(u8id,nccid,u8name,name,
            is_depart=is_depart,is_cash_flow=is_cash_flow,is_busi_type=is_busi_type,
            is_trader=is_trader)
            AS.List[u8id] = ss

def load_account_voucher(excel_path, owner):
    """
    加载U8凭证信息
    """
    errowb = Workbook()
    book = openpyxl.load_workbook(excel_path)
    sheet = book.active
    i=0
    for row in sheet.iter_rows(min_row=2, min_col=1):    
        vnum = row[2].value
        date = row[0].value
        desc = row[4].value
        u8id = row[5].value
        debit_orig = row[6].value
        debit_local = debit_orig
        credit_orig = row[7].value
        credit_local = credit_orig
        depart = row[15].value
        trader = row[65].value
        i=i+1
        if u8id in AS.List:
            ss = AS.List[u8id]
            av = AV(vnum, owner,date,desc,ss,debit_orig,debit_local,credit_orig,credit_local,depart,trader)
            AV.List.append(av)
            #增加客商判断
            if av.trader not in Trader.List:
                r = [cell.value for cell in row]
                r.append("找不到客商信息")
                errowb.active.append(r)
    errowb.save(DEFAULT_U8_ERROR_OUTPUT)
            
def main():
    # init commandline args
    parser = argparse.ArgumentParser(description='ncc importer')
    parser.add_argument('-i', '--input', type=str, required=False, help='需要导入U8凭证')
    parser.add_argument('-o', '--output', type=str, required=False,
                        default=DEFAULT_NCC_OUTPUT,
                        metavar="/path/to/output/",
                        help='output file for ncc importer')
    args = parser.parse_args()
    filename = args.input
    if filename == None or len(filename) == 0:
        filename = tkinter.filedialog.askopenfilename()
    if filename == None or len(filename) == 0:
        parser.print_help()
        return

    log.logger.debug("begin convert u8 accounting voucher to ncc format")
    load_traders()
    log.logger.debug("load {0}'s traders".format(len(Trader.List))) 
    load_projects()
    log.logger.debug("load {0}'s projects".format(len(Project.List)))
    load_account_subjects()
    log.logger.debug("load {0}'s account subjects".format(len(AS.List)))
    load_account_voucher(filename, "li xin1")
    book = openpyxl.load_workbook(resource_path(os.path.join("res","empty.xlsx")))
    sheet = book.active
    i=0
    for av in AV.List:
        i=i+1
        # if int(av.vn) not in INC:
        #     continue
        nccid = av.account_subject.nccid
        a1 = None
        a2 = None
        a3 = None
        a4 = None
        if nccid in ASB:
            a1 = ASB[nccid]+":银行账户"
        elif av.account_subject.is_depart and av.depart:
            a1 = av.depart+":部门"
        if av.account_subject.is_busi_type:
            a4 = "080104:业务类型"
        if av.account_subject.is_trader and av.trader is not None:
            if av.trader in Trader.List:
                a3 = str(Trader.List[av.trader].txid)+":客商"
            else:
                log.logger.debug("trader {0} not match".format(av.trader))
        if av.account_subject.is_cash_flow:
            isFive = re.search("归集|拨款|划转", av.desc) is not None
            if isFive:
                a2 = "010202:现金流量项目"
            elif av.isDebit(): #是否借贷
                a2 = "010101:现金流量项目"
            else:
                a2 = "010201:现金流量项目"
        row = [i,av.ab,av.vtype,av.vn,av.owner,
        av.date,av.desc,nccid,av.ctype,
        av.debit_orig,av.debit_local,None,None,None,
        av.credit_orig,av.credit_local, a1,a2,a3,a4]
        sheet.append(row)    
    book.save(args.output)
    messagebox.showinfo("ncc importer", '处理完毕，生成文件:'+args.output)
if __name__ == "__main__" :
    main()